package com.sinopac.model.RptQuery;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import netfusion.jbranch.database.DataAccessManager;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.sinopac.entity.RptQuery.CompetentRecordVO;
import com.sinopac.entity.RptQuery.EmployeeRecordVO;
import com.sinopac.entity.RptQuery.MoratoriumListVO;
import com.sinopac.entity.RptQuery.PersonalRecordVO;
import com.sinopac.entity.RptQuery.TokenListVO;
import com.sinopac.entity.RptQuery.UnusualReportVO;
import com.sinopac.model.core.ModelCore;
import com.sinopac.util.PropsUtil;

public class RptQueryModel extends ModelCore{
	SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");

	//查詢異常報表Model
	public List<UnusualReportVO> getUnusualReport(String from_i,String to_i){
		DataAccessManager dam = new DataAccessManager();
		List<UnusualReportVO> uusList = new ArrayList<UnusualReportVO>();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Map<String,String> status = new HashMap<String, String>();
		status.put("00", "未配發");
		status.put("01", "正常");
		status.put("02", "已暫禁");
		status.put("03", "已註銷");
		status.put("04", "異動中,待覆核");
		status.put("05", "申請中,待覆核");
		try {
			String sql = "SELECT token_esn,customer_id,unisys_status,token_status,log_time FROM Logs WHERE log_time BETWEEN :from_i AND :to_i AND job_code='6002'";
			dam.setObject("from_i", from_i + " 00:00:00");
			dam.setObject("to_i", to_i + " 23:59:59");
			dam.setQueryString(sql);
			List<Map<String,Object>> list = dam.exeQuery();
			if(!list.isEmpty()){
				for(Map<String,Object> map:list){
					UnusualReportVO unusualReportVO = new UnusualReportVO();
					unusualReportVO.setToken_esn((String)map.get("token_esn"));
					unusualReportVO.setCustomer_id((String)map.get("customer_id"));
					unusualReportVO.setUnisys_status(status.get((String)map.get("unisys_status")));
					unusualReportVO.setToken_status(status.get((String)map.get("token_status")));
					unusualReportVO.setLog_time(map.get("log_time") != null?df.format((Timestamp)map.get("log_time")):"");
					uusList.add(unusualReportVO);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return uusList ;
	}
	
	//異常報表匯出成Excel
	public File exportUnusualReport(List<UnusualReportVO> uusList, String sort) throws Exception {
		// New Excel Work Book
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		wb.setSheetName(0, sort, HSSFCell.ENCODING_UTF_16);
		int rnum = 0;
		int cnum = 0;
		// Create a row and put some cells in it. Rows are 0 based.
		HSSFRow row = sheet.createRow((short) rnum++);
		// Create a cell and put a value in it.
		HSSFCell cell = row.createCell((short) cnum++);

		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("TOKEN ESN");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("客戶ID");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("UNISYS狀態");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("TOKEN狀態");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("異動日期");
		
		if ((uusList != null) && (uusList.size() > 0)) {
			for (int i = 0; i < uusList.size(); i++) {
				UnusualReportVO vo = uusList.get(i);
				cnum = 0;
				row = sheet.createRow((short) rnum++);

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getToken_esn());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getCustomer_id());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getUnisys_status());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getToken_status());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getLog_time());
			}
			cnum = 0;
			row = sheet.createRow((short) rnum++);
			cell = row.createCell((short) cnum++);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("===== 總共 " + uusList.size() + " 筆資料。");
		}

		// output Excel File
		String filePath = PropsUtil.getConfig("batchFile.path");
		String outputFile = filePath + sort + sdf.format(new Date()) + ".xls";
		return writeFile(outputFile,wb);
	}
	
	//查詢行員作業紀錄Model
	public List<EmployeeRecordVO> getEmployeeRecord(String from_i,String to_i){
		DataAccessManager dam = new DataAccessManager();
		List<EmployeeRecordVO> epyList = new ArrayList<EmployeeRecordVO>();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try{
			String sql = "SELECT l.log_time,l.job_code,l.customer_id,c.customer_name,l.token_esn,l.batch_file,l.operator FROM Logs l LEFT JOIN Customers c ON c.customer_id = l.customer_id WHERE log_time BETWEEN :from_i AND :to_i";
			dam.setObject("from_i", from_i + " 00:00:00");
			dam.setObject("to_i", to_i + " 23:59:59");
			dam.setQueryString(sql);
			List<Map<String,Object>> list = dam.exeQuery();
			Map<String,String> job_codeMap = new HashMap<String, String>();
			job_codeMap.put("1000", "配發TOKEN");
			job_codeMap.put("1001", "啟用TOKEN");
			job_codeMap.put("1002", "暫禁TOKEN");
			job_codeMap.put("1003", "解禁TOKEN");
			job_codeMap.put("1004", "註銷TOKEN");
			job_codeMap.put("1005", "同步TOKEN");
			job_codeMap.put("2000", "批次配發TOKEN");
			job_codeMap.put("3000", "TOKEN入庫");
			job_codeMap.put("4000", "客戶加入集團戶");
			job_codeMap.put("4001", "客戶退出集團戶");
			job_codeMap.put("4002", "設定客戶為集團戶主帳號");
			job_codeMap.put("4003", "設定客戶為集團戶子帳號");
			job_codeMap.put("5000", ".NET API驗證OTP");
			job_codeMap.put("5001", ".NET API查詢客戶狀態");
			job_codeMap.put("6000", "資料抄寫開始");
			job_codeMap.put("6001", "資料抄寫結束");
			job_codeMap.put("6002", "異常紀錄");
			
			if(!list.isEmpty()){
				for(Map<String,Object> map:list){
					EmployeeRecordVO employeeRecordVO = new EmployeeRecordVO();
					employeeRecordVO.setLog_time(map.get("log_time") != null?df.format((Timestamp)map.get("log_time")):"");
					employeeRecordVO.setJob_code(job_codeMap.get((String)map.get("job_code")));
					employeeRecordVO.setCustomer_id((String)map.get("customer_id"));					
					employeeRecordVO.setCustomer_name((String)map.get("customer_name"));
					employeeRecordVO.setToken_esn((String)map.get("token_esn"));
					employeeRecordVO.setBatch_file((String)map.get("batch_file"));
					employeeRecordVO.setOperator((String)map.get("operator"));
					epyList.add(employeeRecordVO);
				}
			}	
		}catch(SQLException e){
			e.printStackTrace();
		}
		return epyList;
	}
	
	//行員作業紀錄匯出成Excel
	public File exportEmployeeRecord(List<EmployeeRecordVO> epyList, String sort) throws Exception {
		// New Excel Work Book
		File fileName = null;
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		wb.setSheetName(0, sort, HSSFCell.ENCODING_UTF_16);
		int rnum = 0;
		int cnum = 0;
		// Create a row and put some cells in it. Rows are 0 based.
		HSSFRow row = sheet.createRow((short) rnum++);
		// Create a cell and put a value in it.
		HSSFCell cell = row.createCell((short) cnum++);

		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("日期");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("作業");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("客戶ID");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("客戶名稱");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("TOKEN ESN");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("檔案名稱");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("異動經辦");
		
		if ((epyList != null) && (epyList.size() > 0)) {
			for (int i = 0; i < epyList.size(); i++) {
				EmployeeRecordVO vo = epyList.get(i);
				cnum = 0;
				row = sheet.createRow((short) rnum++);

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getLog_time());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getJob_code());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getCustomer_id());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getCustomer_name());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getToken_esn());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getBatch_file());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getOperator());
			}
			cnum = 0;
			row = sheet.createRow((short) rnum++);
			cell = row.createCell((short) cnum++);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("===== 總共 " + epyList.size() + " 筆資料。");
		}

		// output Excel File
		String filePath = PropsUtil.getConfig("batchFile.path");
		//2013-01-10 新增如無資料夾自行創建
//		File file = new File(filePath);
//		if(!file.exists()){
//			file.mkdirs();
//		}
		String outputFile = filePath + sort + sdf.format(new Date()) + ".xls";
		return writeFile(outputFile,wb);
	}
	
	//查詢主管覆核紀錄Model
	public List<CompetentRecordVO> getCompetentRecord(String from_i,String to_i){
		DataAccessManager dam = new DataAccessManager();
		List<CompetentRecordVO> cptList = new ArrayList<CompetentRecordVO>();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try{
			String sql = "SELECT l.log_time,l.job_code,l.customer_id,c.customer_name,l.token_esn,l.batch_file,l.manager,l.returncode,l.comment FROM Logs l LEFT JOIN Customers c ON c.customer_id = l.customer_id WHERE l.job_code != '3000' AND l.log_time BETWEEN :from_i AND :to_i";
			dam.setObject("from_i", from_i + " 00:00:00");
			dam.setObject("to_i", to_i + " 23:59:59");
			dam.setQueryString(sql);
			List<Map<String,Object>> list = dam.exeQuery();
			Map<String,String> job_codeMap = new HashMap<String, String>();
			job_codeMap.put("1000", "配發TOKEN");
			job_codeMap.put("1001", "啟用TOKEN");
			job_codeMap.put("1002", "暫禁TOKEN");
			job_codeMap.put("1003", "解禁TOKEN");
			job_codeMap.put("1004", "註銷TOKEN");
			job_codeMap.put("1005", "同步TOKEN");
			job_codeMap.put("2000", "批次配發TOKEN");
			job_codeMap.put("3000", "TOKEN入庫");
			job_codeMap.put("4000", "客戶加入集團戶");
			job_codeMap.put("4001", "客戶退出集團戶");
			job_codeMap.put("4002", "設定客戶為集團戶主帳號");
			job_codeMap.put("4003", "設定客戶為集團戶子帳號");
			job_codeMap.put("5000", ".NET API驗證OTP");
			job_codeMap.put("5001", ".NET API查詢客戶狀態");
			job_codeMap.put("6000", "資料抄寫開始");
			job_codeMap.put("6001", "資料抄寫結束");
			job_codeMap.put("6002", "異常紀錄");
			
			if(!list.isEmpty()){
				for(Map<String,Object> map:list){
					CompetentRecordVO competentRecordVO = new CompetentRecordVO();
					competentRecordVO.setLog_time(map.get("log_time") != null?df.format((Timestamp)map.get("log_time")):"");
					competentRecordVO.setJob_code(job_codeMap.get((String)map.get("job_code")));
					competentRecordVO.setCustomer_id((String)map.get("customer_id"));
					competentRecordVO.setCustomer_name((String)map.get("customer_name"));
					competentRecordVO.setToken_esn((String)map.get("token_esn"));
					competentRecordVO.setBatch_file((String)map.get("batch_file"));
					competentRecordVO.setManager((String)map.get("manager"));
					competentRecordVO.setReturncode((String)map.get("returncode"));
					competentRecordVO.setComment((String)map.get("comment"));
					cptList.add(competentRecordVO);
				}
			}
			
			dam = new DataAccessManager();
			String sql_2 = "SELECT log_time,job_code,token_esn,batch_file,manager,returncode,comment FROM Logs WHERE job_code = '3000' AND(log_time BETWEEN :from_i AND :to_i)";
			dam.setObject("from_i", from_i + " 00:00:00");
			dam.setObject("to_i", to_i + " 23:59:59");
			dam.setQueryString(sql_2);
			List<Map<String,Object>> list_2 = dam.exeQuery();
			if(!list_2.isEmpty()){
				for(Map<String,Object> map:list_2){
					CompetentRecordVO competentRecordVO = new CompetentRecordVO();
					competentRecordVO.setLog_time(map.get("log_time") != null?df.format((Timestamp)map.get("log_time")):"");
					competentRecordVO.setJob_code(job_codeMap.get((String)map.get("job_code")));
					competentRecordVO.setToken_esn((String)map.get("token_esn"));
					competentRecordVO.setBatch_file((String)map.get("batch_file"));
					competentRecordVO.setManager((String)map.get("manager"));
					competentRecordVO.setReturncode((String)map.get("returncode"));
					competentRecordVO.setComment((String)map.get("comment"));
					cptList.add(competentRecordVO);
				}
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		return cptList;
	}
	
	//主管覆核紀錄匯出成Excel
	public File exportCompetentRecord(List<CompetentRecordVO> cptList, String sort) throws Exception {
		// New Excel Work Book
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		wb.setSheetName(0, sort, HSSFCell.ENCODING_UTF_16);
		int rnum = 0;
		int cnum = 0;
		// Create a row and put some cells in it. Rows are 0 based.
		HSSFRow row = sheet.createRow((short) rnum++);
		// Create a cell and put a value in it.
		HSSFCell cell = row.createCell((short) cnum++);

		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("日期");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("作業");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("客戶ID");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("客戶名稱");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("TOKEN ESN");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("檔案名稱");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("覆核主管");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("覆核結果");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("註解");
		
		if ((cptList != null) && (cptList.size() > 0)) {
			for (int i = 0; i < cptList.size(); i++) {
				CompetentRecordVO vo = cptList.get(i);
				cnum = 0;
				row = sheet.createRow((short) rnum++);

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getLog_time());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getJob_code());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getCustomer_id());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getCustomer_name());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getToken_esn());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getBatch_file());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getManager());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getReturncode());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getComment());
			}
			cnum = 0;
			row = sheet.createRow((short) rnum++);
			cell = row.createCell((short) cnum++);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("===== 總共 " + cptList.size() + " 筆資料。");
		}

		// output Excel File
		String filePath = PropsUtil.getConfig("batchFile.path");
		//2013-01-10 新增如無資料夾自行創建
//		File file = new File(filePath);
//		if(!file.exists()){
//			file.mkdirs();
//		}
		String outputFile = filePath + sort + sdf.format(new Date()) + ".xls";
//		FileOutputStream fOut = new FileOutputStream(outputFile);
//		wb.write(fOut);
//		fOut.flush();
		return writeFile(outputFile,wb);
	}
	
	//查詢TOKEN庫存清單計算Count
	public List<String> getCount(String from_i,String to_i){
		List<String> countList = new  ArrayList<String>();
		try{
			DataAccessManager dam = new DataAccessManager();
			String sql_1 = "select COUNT(token_status) t from Tokens";
			dam.setQueryString(sql_1);
			List<Map<String, Object>> list_sum = dam.exeQuery();
			String count_sum = list_sum.get(0).get("t").toString();
			countList.add(count_sum);
			
			dam = new DataAccessManager();
			String sql_2 = "select COUNT(token_status) t from Tokens where token_status = '00' or token_status = '05'";
			dam.setQueryString(sql_2);
			List<Map<String, Object>> list_1 = dam.exeQuery();
			String count_1 = list_1.get(0).get("t").toString();
			countList.add(count_1);
			
			dam = new DataAccessManager();
			String sql_3 = "select COUNT(token_status) t from Tokens where token_status = '01' or token_status = '02' or token_status = '04'";
			dam.setQueryString(sql_3);
			List<Map<String, Object>> list_2 = dam.exeQuery();
			String count_2 = list_2.get(0).get("t").toString();
			countList.add(count_2);
			
			dam = new DataAccessManager();
			String sql_4 = "select COUNT(token_status) t from Tokens where token_status = '03'";
			dam.setQueryString(sql_4);
			List<Map<String, Object>> list_3 = dam.exeQuery();
			String count_3 = list_3.get(0).get("t").toString();
			countList.add(count_3);
			
		}catch(SQLException e){
			e.printStackTrace();
		}
		return countList;
	}
	
	//查詢TOKEN庫存清單Model
	public List<TokenListVO> getTokenList(String from_i,String to_i){
		DataAccessManager dam = new DataAccessManager();
		List<TokenListVO> tokenList = new ArrayList<TokenListVO>();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Map<String,String> tokenStatus = new HashMap<String, String>();
		tokenStatus.put("00", "未配發");
		tokenStatus.put("01", "正常");
		tokenStatus.put("02", "已暫禁");
		tokenStatus.put("03", "已註銷");
		tokenStatus.put("04", "異動中,待覆核");
		tokenStatus.put("05", "申請中,待覆核");
		
		try {
			// TokenListVO countListVO = new TokenListVO();
//			String sql = "SELECT t.token_esn,t.token_status,l.customer_id,t.created_time from Tokens t inner join Logs l on t.token_esn = l.token_esn order by t.token_esn";
			StringBuffer query1 = new StringBuffer();
			query1.append("select t.token_esn,t.created_time,t.token_status, ");
			query1.append("case ");
			query1.append("when tc.is_related_to_group = '0' ");
			query1.append("then (select customer_id from Customers where customer_id = tc.related_to) ");
			query1.append("when tc.is_related_to_group = '1' ");
			query1.append("then (select customer_id from Customers where group_id = tc.related_to and is_group_main = '1' ) ");
			query1.append("end customer_id ");
			query1.append("from tokens t left join Token_Customer tc on t.token_esn = tc.token_esn order by t.token_status,t.token_esn ");
			dam.setQueryString(query1.toString());
			List<Map<String,Object>> list = dam.exeQuery();
			if(!list.isEmpty()){
				for(Map<String,Object> map:list){
					TokenListVO tokenListVO = new TokenListVO();
					tokenListVO.setToken_esn((String)map.get("token_esn"));
					if(StringUtils.equals((String)map.get("token_status"), "00") && map.get("customer_id") != null){
						tokenListVO.setToken_status("未啟用");
					}else{
						tokenListVO.setToken_status(tokenStatus.get((String)map.get("token_status")));
					}
					tokenListVO.setCustomer_id((String)map.get("customer_id"));
					tokenListVO.setCreated_time(map.get("created_time") != null?df.format((Timestamp)map.get("created_time")):"");
					tokenList.add(tokenListVO);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tokenList;
	}
	
	//TOKEN庫存清單匯出成Excel
	public File exportTokenList(List<TokenListVO> tokenList, String sort) throws Exception {
		// New Excel Work Book
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		wb.setSheetName(0, sort, HSSFCell.ENCODING_UTF_16);
		int rnum = 0;
		int cnum = 0;
		// Create a row and put some cells in it. Rows are 0 based.
		HSSFRow row = sheet.createRow((short) rnum++);
		// Create a cell and put a value in it.
		HSSFCell cell = row.createCell((short) cnum++);

		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("TOKEN ESN");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("狀態");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("客戶ID");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("入庫日期");
		
		if ((tokenList != null) && (tokenList.size() > 0)) {
			for (int i = 0; i < tokenList.size(); i++) {
				TokenListVO vo = tokenList.get(i);
				cnum = 0;
				row = sheet.createRow((short) rnum++);

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getToken_status());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getToken_esn());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getCustomer_id());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getCreated_time());
			}
			cnum = 0;
			row = sheet.createRow((short) rnum++);
			cell = row.createCell((short) cnum++);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("===== 總共 " + tokenList.size() + " 筆資料。");
		}

		// output Excel File
		String filePath = PropsUtil.getConfig("batchFile.path");
		//2013-01-10 新增如無資料夾自行創建
//		File file = new File(filePath);
//		if(!file.exists()){
//			file.mkdirs();
//		}
		String outputFile = filePath + sort + sdf.format(new Date()) + ".xls";
		return writeFile(outputFile,wb);
	}
	
	//查詢驗證失敗被暫禁清單Model
	public List<MoratoriumListVO> getMoratoriumList(String from_i,String to_i ){
		DataAccessManager dam = new DataAccessManager();
		List<MoratoriumListVO> mrtiList = new ArrayList<MoratoriumListVO>();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Map<String,String> tokenStatus = new HashMap<String, String>();
		tokenStatus.put("02", "暫禁");
		try {
			String sql = "SELECT t.updated_time,l.customer_id,c.customer_name,t.token_esn,t.token_status from Tokens t inner join Verify_Logs l on t.token_esn = l.token_esn inner join Customers c on l.customer_id = c.customer_id WHERE t.token_status = '02' AND l.job_code = '5000' AND l.returncode = '110000'";
			dam.setQueryString(sql);
			List<Map<String,Object>> list = dam.exeQuery();
			if(!list.isEmpty()){
				for(Map<String,Object> map:list){
					MoratoriumListVO moratoriumListVO = new MoratoriumListVO();
					moratoriumListVO.setUpdated_time(map.get("updated_time") != null?df.format((Timestamp)map.get("updated_time")):"");
					moratoriumListVO.setCustomer_id((String)map.get("customer_id"));
					moratoriumListVO.setCustomer_name((String)map.get("customer_name"));
					moratoriumListVO.setToken_esn((String)map.get("token_esn"));
					moratoriumListVO.setToken_status(tokenStatus.get((String)map.get("token_status")));
					mrtiList.add(moratoriumListVO);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return mrtiList;
	}
	
	//TOKEN庫存清單匯出成Excel
	public File exportMoratoriumList(List<MoratoriumListVO> mrtiList, String sort) throws Exception {
		// New Excel Work Book
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		wb.setSheetName(0, sort, HSSFCell.ENCODING_UTF_16);
		int rnum = 0;
		int cnum = 0;
		// Create a row and put some cells in it. Rows are 0 based.
		HSSFRow row = sheet.createRow((short) rnum++);
		// Create a cell and put a value in it.
		HSSFCell cell = row.createCell((short) cnum++);

		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("暫禁日期");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("客戶ID");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("客戶名稱");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("TOKEN ESN");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("TOKEN狀態");
		
		if ((mrtiList != null) && (mrtiList.size() > 0)) {
			for (int i = 0; i < mrtiList.size(); i++) {
				MoratoriumListVO vo = mrtiList.get(i);
				cnum = 0;
				row = sheet.createRow((short) rnum++);

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getUpdated_time());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getCustomer_id());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getCustomer_name());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getToken_esn());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getToken_status());
			}
			cnum = 0;
			row = sheet.createRow((short) rnum++);
			cell = row.createCell((short) cnum++);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("===== 總共 " + mrtiList.size() + " 筆資料。");
		}

		// output Excel File
		String filePath = PropsUtil.getConfig("batchFile.path");
		//2013-01-10 新增如無資料夾自行創建
//		File file = new File(filePath);
//		if(!file.exists()){
//			file.mkdirs();
//		}
		String outputFile = filePath + sort + sdf.format(new Date()) + ".xls";
//		FileOutputStream fOut = new FileOutputStream(outputFile);
//		wb.write(fOut);
//		fOut.flush();
		return writeFile(outputFile,wb);
	}
	
	//查詢個人作業紀錄Model
	public List<CompetentRecordVO> getPersonalRecord(String userName){
		DataAccessManager dam = new DataAccessManager();
		List<CompetentRecordVO> psrList = new ArrayList<CompetentRecordVO>();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try{
			String sql = "SELECT l.log_time,l.job_code,l.customer_id,c.customer_name,l.token_esn,l.batch_file,l.manager,l.returncode,l.comment FROM Logs l LEFT JOIN Customers c ON c.customer_id = l.customer_id WHERE (l.operator = :userName OR l.manager = :userName) AND l.log_time BETWEEN convert(varchar(10),getdate()-30,120) AND getdate();";
			dam.setObject("userName", userName);
			dam.setQueryString(sql);
			List<Map<String,Object>> list = dam.exeQuery();
			Map<String,String> job_codeMap = new HashMap<String, String>();
			job_codeMap.put("1000", "配發TOKEN");
			job_codeMap.put("1001", "啟用TOKEN");
			job_codeMap.put("1002", "暫禁TOKEN");
			job_codeMap.put("1003", "解禁TOKEN");
			job_codeMap.put("1004", "註銷TOKEN");
			job_codeMap.put("1005", "同步TOKEN");
			job_codeMap.put("2000", "批次配發TOKEN");
			job_codeMap.put("3000", "TOKEN入庫");
			job_codeMap.put("4000", "客戶加入集團戶");
			job_codeMap.put("4001", "客戶退出集團戶");
			job_codeMap.put("4002", "設定客戶為集團戶主帳號");
			job_codeMap.put("4003", "設定客戶為集團戶子帳號");
			job_codeMap.put("5000", ".NET API驗證OTP");
			job_codeMap.put("5001", ".NET API查詢客戶狀態");
			job_codeMap.put("6000", "資料抄寫開始");
			job_codeMap.put("6001", "資料抄寫結束");
			job_codeMap.put("6002", "異常紀錄");
			
			if(!list.isEmpty()){
				for(Map<String,Object> map:list){
					CompetentRecordVO competentRecordVO = new CompetentRecordVO();
					competentRecordVO.setLog_time(map.get("log_time") != null?df.format((Timestamp)map.get("log_time")):"");
					competentRecordVO.setJob_code(job_codeMap.get((String)map.get("job_code")));
					competentRecordVO.setCustomer_id((String)map.get("customer_id"));
					competentRecordVO.setCustomer_name((String)map.get("customer_name"));
					competentRecordVO.setToken_esn((String)map.get("token_esn"));
					competentRecordVO.setBatch_file((String)map.get("batch_file"));
					competentRecordVO.setReturncode((String)map.get("returncode"));
					competentRecordVO.setManager((String)map.get("manager"));
					competentRecordVO.setComment((String)map.get("comment"));
					psrList.add(competentRecordVO);
				}
			}	
		}catch(SQLException e){
			e.printStackTrace();
		}
		return psrList;
	}
	
	public List<PersonalRecordVO> getJobQueuesRecord(String userName){
		DataAccessManager dam = new DataAccessManager();
		List<PersonalRecordVO> jqList = new ArrayList<PersonalRecordVO>();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try{
			String sql = "SELECT j.submitted_time,j.job_code,j.customer_id,c.customer_name,j.token_esn,j.batch_file FROM Job_queues j LEFT JOIN Customers c ON c.customer_id = j.customer_id WHERE j.operator = :userName AND j.submitted_time BETWEEN convert(varchar(10),getdate()-30,120) AND getdate();";
			dam.setObject("userName", userName);
			dam.setQueryString(sql);
			List<Map<String,Object>> list = dam.exeQuery();
			Map<String,String> job_codeMap = new HashMap<String, String>();
			job_codeMap.put("1000", "配發TOKEN");
			job_codeMap.put("1001", "啟用TOKEN");
			job_codeMap.put("1002", "暫禁TOKEN");
			job_codeMap.put("1003", "解禁TOKEN");
			job_codeMap.put("1004", "註銷TOKEN");
			job_codeMap.put("1005", "同步TOKEN");
			job_codeMap.put("2000", "批次配發TOKEN");
			job_codeMap.put("3000", "TOKEN入庫");
			job_codeMap.put("4000", "客戶加入集團戶");
			job_codeMap.put("4001", "客戶退出集團戶");
			job_codeMap.put("4002", "設定客戶為集團戶主帳號");
			job_codeMap.put("4003", "設定客戶為集團戶子帳號");
			job_codeMap.put("5000", ".NET API驗證OTP");
			job_codeMap.put("5001", ".NET API查詢客戶狀態");
			job_codeMap.put("6000", "資料抄寫開始");
			job_codeMap.put("6001", "資料抄寫結束");
			job_codeMap.put("6002", "異常紀錄");
			
			if(!list.isEmpty()){
				for(Map<String,Object> map:list){
					PersonalRecordVO personalRecordVO = new PersonalRecordVO();
					personalRecordVO.setSubmitted_time(map.get("submitted_time") != null?df.format((Timestamp)map.get("submitted_time")):"");
					personalRecordVO.setJob_code(job_codeMap.get((String)map.get("job_code")));
					personalRecordVO.setCustomer_id((String)map.get("customer_id"));
					personalRecordVO.setCustomer_name((String)map.get("customer_name"));
					personalRecordVO.setToken_esn((String)map.get("token_esn"));
					personalRecordVO.setBatch_file((String)map.get("batch_file"));
					jqList.add(personalRecordVO);
				}
			}	
		}catch(SQLException e){
			e.printStackTrace();
		}
		return jqList ;
	}
	
	//個人作業紀錄匯出成Excel
	public File exportPersonalRecord(List<CompetentRecordVO> psrList, List<PersonalRecordVO> jqList, String userName) throws Exception {
		// New Excel Work Book
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		HSSFSheet sheet_2 = wb.createSheet();
		wb.setSheetName(0, userName + "已覆核作業紀錄清單", HSSFCell.ENCODING_UTF_16);
		wb.setSheetName(1, userName + "待覆核作業紀錄清單", HSSFCell.ENCODING_UTF_16);
		int rnum = 0;
		int cnum = 0;
		int rnum_2 = 0;
		int cnum_2 = 0;
		// Create a row and put some cells in it. Rows are 0 based.
		HSSFRow row = sheet.createRow((short) rnum++);
		HSSFRow row_2 = sheet_2.createRow((short) rnum_2++);
		// Create a cell and put a value in it.
		HSSFCell cell = row.createCell((short) cnum++);
		HSSFCell cell_2 = row_2.createCell((short) cnum_2++);

		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("日期");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("作業");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("客戶ID");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("客戶名稱");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("TOKEN ESN");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("檔案名稱");

		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("覆核結果");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("覆核主管");
		
		cell = row.createCell((short) cnum++);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue("註解");
		
		cell_2.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell_2.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell_2.setCellValue("日期");
		
		cell_2 = row_2.createCell((short) cnum_2++);
		cell_2.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell_2.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell_2.setCellValue("作業");
		
		cell_2 = row_2.createCell((short) cnum_2++);
		cell_2.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell_2.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell_2.setCellValue("客戶ID");

		cell_2 = row_2.createCell((short) cnum_2++);
		cell_2.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell_2.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell_2.setCellValue("客戶名稱");
		
		cell_2 = row_2.createCell((short) cnum_2++);
		cell_2.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell_2.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell_2.setCellValue("TOKEN ESN");

		cell_2 = row_2.createCell((short) cnum_2++);
		cell_2.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell_2.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell_2.setCellValue("檔案名稱");
		
		if ((psrList != null) && (psrList.size() > 0)) {
			for (int i = 0; i < psrList.size(); i++) {
				CompetentRecordVO vo = psrList.get(i);
				cnum = 0;
				row = sheet.createRow((short) rnum++);

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getLog_time());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getJob_code());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getCustomer_id());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getCustomer_name());

				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getToken_esn());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getBatch_file());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getReturncode());
								
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getManager());
				
				cell = row.createCell((short) cnum++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(vo.getComment());
			}
			cnum = 0;
			row = sheet.createRow((short) rnum++);
			cell = row.createCell((short) cnum++);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("===== 總共 " + psrList.size() + " 筆資料。");
		}
		
		if ((jqList != null) && (jqList.size() > 0)) {
			for (int i = 0; i < jqList.size(); i++) {
				PersonalRecordVO vo = jqList.get(i);
				cnum_2 = 0;
				row_2 = sheet_2.createRow((short) rnum_2++);

				cell_2 = row_2.createCell((short) cnum_2++);
				cell_2.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell_2.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell_2.setCellValue(vo.getSubmitted_time());

				cell_2 = row_2.createCell((short) cnum_2++);
				cell_2.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell_2.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell_2.setCellValue(vo.getJob_code());

				cell_2 = row_2.createCell((short) cnum_2++);
				cell_2.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell_2.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell_2.setCellValue(vo.getCustomer_id());
				
				cell_2 = row_2.createCell((short) cnum_2++);
				cell_2.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell_2.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell_2.setCellValue(vo.getCustomer_name());

				cell_2 = row_2.createCell((short) cnum_2++);
				cell_2.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell_2.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell_2.setCellValue(vo.getToken_esn());
				
				cell_2 = row_2.createCell((short) cnum_2++);
				cell_2.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell_2.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell_2.setCellValue(vo.getBatch_file());
								
			}
			cnum_2 = 0;
			row_2 = sheet_2.createRow((short) rnum_2++);
			cell_2 = row_2.createCell((short) cnum_2++);
			cell_2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell_2.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell_2.setCellValue("===== 總共 " + jqList.size() + " 筆資料。");
		}
		// output Excel File
		String filePath = PropsUtil.getConfig("batchFile.path");
		//2013-01-10 新增如無資料夾自行創建
//		File file = new File(filePath);
//		if(!file.exists()){
//			file.mkdirs();
//		}
		String outputFile = filePath + userName +"個人作業紀錄"+ sdf.format(new Date()) + ".xls";
//		FileOutputStream fOut = new FileOutputStream(outputFile);
//		wb.write(fOut);
//		fOut.flush();
		return writeFile(outputFile,wb);
	}
	
	private File writeFile(String filePath,HSSFWorkbook wb) throws Exception{
		// output Excel File
		File file = new File(filePath);
		String outputFile = filePath;
		FileOutputStream fOut = new FileOutputStream(outputFile);
		wb.write(fOut);
		fOut.flush();
		return file;
	}
}
